set spark.job.priority=VERY_HIGH;
set spark.sql.hive.convertInsertingPartitionedTable=false;
set spark.hadoop.hive.exec.dynamic.partition=true;
set spark.hadoop.hive.exec.dynamic.partition.mode=nostrict;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;
set spark.sql.shuffle.partitions=100000;
set spark.executor.memoryOverhead=8192;
set spark.shuffle.compress=true;
set spark.dynamicAllocation.maxExecutors=100000;
set spark.sql.autoBroadcastJoinThreshold=221440000;
SET spark.sql.adaptive.enabled=true;
SET spark.sql.adaptive.shuffle.targetPostShuffleInputSize=536870912;
set spark.sql.adaptive.shuffle.targetPostShuffleRowCount=20000000;
set spark.sql.adaptive.shuffle.targetPostShuffleInputSize=67108864;
set hive.merge.mapredfiles = true;
set hive.merge.mapfiles=true;
set hive.merge.mapredfiles = true;
set hive.merge.mapfiles=true;
set hive.new.job.grouping.set.cardinality=2000;
set spark.sql.adaptive.maxNumPostShufflePartitions=100000;
set spark.sql.adaptive.skewedJoin.enabled=true;
set spark.sql.adaptive.skewedPartitionMaxSplits=1000;
set spark.sql.adaptive.skewedPartitionRowCountThreshold=10000000;
set spark.sql.adaptive.skewedPartitionSizeThreshold=52428800;
set spark.sql.mergeSmallFileSize=536870912;
set spark.sql.inMemoryColumnarStorage.compressed=true;
set spark.sql.inMemoryColumnarStorage.batchSize=100000;

insert overwrite table hdp_lbg_supin_zplisting.app_zp_wb_n_flow_action_cube partition (dt='${dateSuffix}',action='click')
select
  t1.source1,t1.platform,t1.slot,t1.disp_local1,t1.disp_cate2,t1.sellingcitytag,t1.requesttype,t1.search_pos,t1.info_buid_type,t1.info_local1,t1.info_cate2
  ,0,0,0,0,0,0,0 -- search
  -- ,search_pv,search_epv,search_as,search_uv,search_infocnt,search_busercnt,search_comcnt

  ,0,0,0,0,0,0 -- exp
  -- ,exp_as,exp_uv,exp_epv,exp_busercnt,exp_infocnt,exp_comcnt

  -- ,0,0,0,0,0,0,0 -- click
  ,click_cnt,click_uv,click_as,click_epv,click_infocnt,click_comcnt,click_busercnt

  ,0,0,0,0,0,0,0,0 --delivery
  -- ,delivery_cnt,delivery_epv,delivery_as,delivery_uv,devresume_cnt,delivery_infocnt,delivery_comcnt,delivery_busercnt

  ,0,0,0,0,0,0,0,0,0,0 -- im
  -- ,im_cnt,im_ack_cnt,im_read_cnt,im_reply_cnt,im_epv,im_as,im_uv,im_infocnt,im_comcnt,im_busercnt

  ,0,0,0,0,0,0,0,0 -- tel
  -- ,tel_cnt,tel_conn_cnt,tel_epv,tel_as,tel_uv,tel_infocnt,tel_comcnt,tel_busercnt

  ,0,0,0,0,0,0 -- link
  -- ,delivery_cnt+tel_cnt+im_cnt as link_cnt,link_bcuv,link_uv,link_infocnt,link_comcnt,link_busercnt

  ,0,0,0,0 --jz/yx
  -- ,jz_cost,jz_cash,yx_cost,yx_cash
  ,regexp_replace(
    regexp_replace(
      concat(
      if(t1.source1    <>'all',                 'source1,','')
      ,if(t1.platform   <>'all',               'platform,','')
      ,if(t1.slot       <>'all',                'slot,'   ,'')
      ,if(t1.disp_local1   <>'all',         'disp_local1,','')
      ,if(t1.disp_cate2    <>'all',         'disp_cate2,' ,'')
      ,if(t1.sellingcitytag  <>'all',    'sellingcitytag,','')
      ,if(t1.requesttype     <>'all',       'requesttype,','')
      ,if(t1.search_pos      <>'all',       'search_pos,' ,'')
      ,if(t1.info_buid_type     <>'all', 'info_buid_type,','')
      ,if(t1.info_local1        <>'all',    'info_local1,','')
      ,if(t1.info_cate2         <>'all',    'info_cate2,' ,'')
      ,if(t1.new_info_cate2         <>'all', 'new_info_cate2,','')
      ,if(t1.new_info_cate3         <>'all', 'new_info_cate3,','')
      ,'all,'
      ),',$',''),',all',''
  ) as dimension
  ,regexp_replace(
    regexp_replace(
    concat(
    if(t1.source1         <>'all',      concat(t1.source1,     ','),'')
    ,if(t1.platform        <>'all',     concat(t1.platform,    ','),'')
    ,if(t1.slot            <>'all',     concat(t1.slot,        ','),'')
    ,if(t1.disp_local1     <>'all',     concat(t1.disp_local1, ','),'')
    ,if(t1.disp_cate2      <>'all',     concat(t1.disp_cate2,  ','),'')
    ,if(t1.sellingcitytag  <>'all',   concat(t1.sellingcitytag,','),'')
    ,if(t1.requesttype     <>'all',   concat(t1.requesttype,   ','),'')
    ,if(t1.search_pos      <>'all',   concat(t1.search_pos,    ','),'')
    ,if(t1.info_buid_type     <>'all',concat(t1.info_buid_type,','),'')
    ,if(t1.info_local1        <>'all',concat(t1.info_local1,   ','),'')
    ,if(t1.info_cate2         <>'all',concat(t1.info_cate2,    ','),'')
    ,if(t1.new_info_cate2 <>'all',concat(t1.new_info_cate2,    ','),'')
    ,if(t1.new_info_cate3 <>'all',concat(t1.new_info_cate3,    ','),'')
    ,'all,'
    ),',$',''),',all',''
  ) as dimension_value
  ,t1.new_info_cate2,t1.new_info_cate3
from
  (
  select
  coalesce(source1,'all') as source1
  ,coalesce(platform,'all') as platform
  ,coalesce(slot,'all') as slot
  ,coalesce(disp_local1,'all') as disp_local1
  ,coalesce(disp_cate2,'all') as disp_cate2
  ,coalesce(sellingcitytag,'all') as sellingcitytag
  ,coalesce(requesttype,'all') as requesttype
  ,coalesce(search_pos,'all') as search_pos
  ,coalesce(info_buid_type,'all') as info_buid_type
  ,coalesce(info_local1,'all') as info_local1
  ,coalesce(info_cate2,'all') as info_cate2
  ,coalesce(new_info_cate2,'all') as new_info_cate2
  ,coalesce(new_info_cate3,'all') as new_info_cate3

  /*
  -- has_disp=1 6,2459,3712
  ,count(distinct if(has_disp=1,sid,null)) as search_pv
  ,count(distinct if(has_disp=1 and info_id>0,sid,null)) as search_epv
  ,sum(if(has_disp=1 and info_id>0,1,0)) as search_as
  ,count(distinct if(has_disp=1 and is_tcb=0,ukey,null)) as search_uv
  ,count(distinct if(has_disp=1 and is_tcb=0 and info_id>0,info_id,null)) as search_infocnt
  ,count(distinct if(has_disp=1 and is_tcb=0 and info_buid>0,info_buid,null)) as search_busercnt
  ,count(distinct if(has_disp=1 and is_tcb=0 and info_etpid>0,info_etpid,null)) as search_comcnt

  -- has_exposure=1 2,6905,3152
  ,count(if(has_exposure=1,s_key,null)) as exp_as
  ,count(distinct if(has_exposure=1,sid,null)) as exp_epv
  ,count(distinct if(has_exposure=1,ukey,null)) as exp_uv
  ,count(distinct if(has_exposure=1,info_id,null)) as exp_infocnt
  ,count(distinct if(has_exposure=1 and is_exp_busercnt=1,info_buid,null)) as exp_busercnt
  ,count(distinct if(has_exposure=1 and is_exp_comcnt=1,info_etpid,null)) as exp_comcnt

  -- has_infodetail=1 -- 1903,5614
  ,count(if(has_infodetail=1,s_key,null)) as click_as
  ,count(distinct if(has_infodetail=1,sid,null)) as click_epv
  ,count(distinct if(has_infodetail=1,info_id,null)) as click_infocnt
  ,count(distinct if(has_infodetail=1,ukey,null)) as click_uv
  ,sum(if(has_infodetail=1,click_cnt,0)) as click_cnt
  ,count(distinct if(has_infodetail=1 and is_exp_busercnt=1,info_buid,null)) as click_busercnt
  ,count(distinct if(has_infodetail=1 and is_exp_comcnt=1,info_etpid,null)) as click_comcnt

  -- has_delivery=1 -- 122,2909
  ,count(if(has_delivery=1,s_key,null)) as delivery_as
  ,count(distinct if(has_delivery=1,sid,null)) as delivery_epv
  ,count(distinct if(has_delivery=1,info_id,null)) as delivery_infocnt
  ,count(distinct if(has_delivery=1,userid,null)) as delivery_uv
  ,count(distinct if(has_delivery=1,delivery_resumeid,null)) as devresume_cnt
  ,sum(if(has_delivery=1,delivery_cnt,0)) as delivery_cnt
  ,count(distinct if(has_delivery=1 and is_exp_busercnt=1,info_buid,null)) as delivery_busercnt
  ,count(distinct if(has_delivery=1 and is_exp_comcnt=1,info_etpid,null)) as delivery_comcnt
  -- has_im=1 -- 617,5046
  ,count(if(has_im=1,s_key,null)) as im_as
  ,count(distinct if(has_im=1,sid,null)) as im_epv
  ,count(distinct if(has_im=1,info_id,null)) as im_infocnt
  ,count(distinct if(has_im=1,userid,null)) as im_uv
  ,count(distinct if(has_im=1 and session_id<>'-',session_id,null)) as im_cnt
  ,count(distinct if(has_im=1 and has_ack=1 and session_id<>'-',session_id,null)) as im_ack_cnt
  ,count(distinct if(has_im=1 and has_read=1 and session_id<>'-',session_id,null)) as im_read_cnt
  ,count(distinct if(has_im=1 and has_reply=1 and session_id<>'-',session_id,null)) as im_reply_cnt
  ,count(distinct if(has_im=1 and is_exp_busercnt=1,info_buid,null)) as im_busercnt
  ,count(distinct if(has_im=1 and is_exp_comcnt=1,info_etpid,null)) as im_comcnt
  -- has_tel=1 -- 21,7341
  ,count(if(has_tel=1,s_key,null)) as tel_as
  ,count(distinct if(has_tel=1,sid,null)) as tel_epv
  ,count(distinct if(has_tel=1,info_id,null)) as tel_infocnt
  ,count(distinct if(has_tel=1,userid,null)) as tel_uv
  ,count(distinct if(has_tel=1 and is_exp_busercnt=1,info_buid,null)) as tel_busercnt
  ,count(distinct if(has_tel=1 and is_exp_comcnt=1,info_etpid,null)) as tel_comcnt
  ,sum(if(has_tel=1,tel_cnt,0)) as tel_cnt
  ,sum(if(has_tel=1,tel_conn_cnt,0)) as tel_conn_cnt
  -- has_link=1 -- 706,0451
  ,count(distinct if(has_link=1,info_id,null)) as link_infocnt
  ,count(distinct if(has_link=1,userid,null)) as link_uv
  ,count(distinct if(has_link=1 and is_exp_busercnt=1,info_buid,null)) as link_busercnt
  ,count(distinct if(has_link=1 and is_exp_comcnt=1,info_etpid,null)) as link_comcnt
  ,count(distinct if(has_link=1 and is_bcuv=1,bcuv_key,null)) as link_bcuv

  -- has_jz+has_yx>0 -- 191,2292
  ,sum(if(has_jz=1,jz_cost,0)) as jz_cost,
  ,sum(if(has_jz=1,jz_cash,0)) as jz_cash,
  ,sum(if(has_yx=1,yx_cost,0)) as yx_cost,
  ,sum(if(has_yx=1,yx_cash,0)) as yx_cash
  */

  -- has_infodetail=1 -- 1903,5614
  ,count(distinct if(has_infodetail=1,s_key,null)) as click_as
  ,count(distinct if(has_infodetail=1,sid,null)) as click_epv
  ,count(distinct if(has_infodetail=1,info_id,null)) as click_infocnt
  ,count(distinct if(has_infodetail=1,ukey,null)) as click_uv
  ,sum(if(has_infodetail=1,click_cnt,0)) as click_cnt
  ,count(distinct if(has_infodetail=1 and is_exp_busercnt=1,info_buid,null)) as click_busercnt
  ,count(distinct if(has_infodetail=1 and is_exp_comcnt=1,info_etpid,null)) as click_comcnt

  from hdp_lbg_supin_zplisting.dm_zp_wb_flow_cube_base where dt='${dateSuffix}' and has_infodetail=1  and has_disp=1
  group by
    source1
    ,platform
    ,slot
    ,disp_local1
    ,disp_cate2
    ,sellingcitytag
    ,requesttype
    ,search_pos
    ,info_buid_type
    ,info_local1
    ,info_cate2
    ,new_info_cate2
    ,new_info_cate3
grouping sets (
      ()
      ,(slot)
      ,(info_cate2)
      ,(info_local1)
      ,(search_pos)
      ,(info_buid_type)
      ,(platform)
      ,(requesttype)
      ,(source1)
      ,(disp_local1)
      ,(disp_cate2)
      ,(sellingcitytag)
      ,(source1,platform)
      ,(platform,info_cate2)
      ,(platform,info_cate2,info_local1)
      ,(platform,info_local1)
      ,(platform,info_buid_type)
      ,(platform,requesttype)
      ,(platform,disp_cate2)
      ,(platform,sellingcitytag)
      ,(platform,disp_local1)
      ,(platform,disp_local1,disp_cate2)
      ,(requesttype,info_cate2)
      ,(requesttype,info_local1,info_cate2)
      ,(requesttype,info_local1)
      ,(requesttype,info_buid_type)
      ,(requesttype,disp_cate2)
      ,(requesttype,disp_local1,disp_cate2)
      ,(requesttype,disp_local1)
      ,(requesttype,sellingcitytag)
      ,(requesttype,slot)
      ,(requesttype,slot,info_cate2)
      ,(requesttype,slot,info_cate2,info_local1)
      ,(requesttype,slot,info_cate2,search_pos)
      ,(requesttype,slot,info_local1)
      ,(requesttype,slot,info_local1,search_pos)
      ,(requesttype,slot,search_pos)
      ,(requesttype,slot,search_pos,info_buid_type)
      ,(requesttype,slot,info_buid_type,info_local1,info_cate2)
      ,(requesttype,slot,disp_cate2)
      ,(requesttype,slot,disp_cate2,search_pos)
      ,(requesttype,slot,disp_local1)
      ,(requesttype,slot,disp_local1,search_pos)
      ,(requesttype,slot,disp_local1,disp_cate2)
      ,(requesttype,slot,disp_local1,disp_cate2,info_buid_type)
      ,(requesttype,source1)
      ,(slot,info_cate2)
      ,(slot,info_local1,info_cate2)
      ,(slot,info_local1)
      ,(slot,info_buid_type)
      ,(slot,disp_local1,disp_cate2)
      ,(slot,sellingcitytag)
      ,(slot,disp_local1)
      ,(slot,disp_cate2)
      ,(slot,source1)
      ,(source1,info_cate2)
      ,(source1,disp_local1,disp_cate2)
      ,(source1,info_local1,info_cate2)
      ,(source1,info_local1)
      ,(source1,info_buid_type)
      ,(source1,disp_cate2)
      ,(source1,sellingcitytag)
      ,(source1,disp_local1)
      ,(disp_cate2,info_buid_type)
      ,(disp_cate2,sellingcitytag)
      ,(disp_cate2,disp_local1)
      ,(disp_local1,info_buid_type)
      ,(info_local1,info_cate2)
      ,(info_buid_type,info_cate2)
      ,(info_buid_type,info_local1)
      ,(sellingcitytag,info_cate2)
      ,(sellingcitytag,info_buid_type)
      ,(new_info_cate2)
      ,(platform,new_info_cate2)
      ,(platform,new_info_cate2,info_local1)
      ,(requesttype,new_info_cate2)
      ,(requesttype,info_local1,new_info_cate2)
      ,(requesttype,slot,new_info_cate2)
      ,(requesttype,slot,new_info_cate2,info_local1)
      ,(requesttype,slot,new_info_cate2,search_pos)
      ,(requesttype,slot,info_buid_type,info_local1,new_info_cate2)
      ,(slot,new_info_cate2)
      ,(slot,info_local1,new_info_cate2)
      ,(source1,new_info_cate2)
      ,(source1,info_local1,new_info_cate2)
      ,(info_local1,new_info_cate2)
      ,(info_buid_type,new_info_cate2)
      ,(sellingcitytag,new_info_cate2)
      ,(new_info_cate3)
      ,(platform,new_info_cate3)
      ,(platform,new_info_cate3,info_local1)
      ,(requesttype,new_info_cate3)
      ,(requesttype,info_local1,new_info_cate3)
      ,(requesttype,slot,new_info_cate3)
      ,(requesttype,slot,new_info_cate3,info_local1)
      ,(requesttype,slot,new_info_cate3,search_pos)
      ,(requesttype,slot,info_buid_type,info_local1,new_info_cate3)
      ,(slot,new_info_cate3)
      ,(slot,info_local1,new_info_cate3)
      ,(source1,new_info_cate3)
      ,(source1,info_local1,new_info_cate3)
      ,(info_local1,new_info_cate3)
      ,(info_buid_type,new_info_cate3)
      ,(sellingcitytag,new_info_cate3)
    )
  )t1
;
